package com.eascs.web.o2o.scheduler.history;

import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.inject.Inject;

import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.stereotype.Service;

import com.alibaba.dubbo.common.utils.StringUtils;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.app.log.Logger;
import com.eascs.app.log.LoggerFactory;
import com.eascs.common.util.DateUtils;
import com.eascs.web.o2o.scheduler.dao.BaseDao;
import com.eascs.web.o2o.scheduler.dao.CrmDao;
import com.eascs.web.o2o.scheduler.dao.ServiceDao;
import com.eascs.web.o2o.scheduler.dao.WorkflowDao;
import com.eascs.web.o2o.scheduler.entity.CustomerShopInfoEntity;
import com.eascs.web.o2o.scheduler.entity.O2oCustomer;
import com.eascs.web.o2o.scheduler.entity.O2oLoanUser;
import com.eascs.web.o2o.scheduler.service.SynchroCrm;
import com.eascs.web.o2o.scheduler.util.Constant;

import jodd.util.StringUtil;

@Service
public class BaseService {

	protected final Logger logger = LoggerFactory.getLogger(getClass());
	@Inject
	BaseDao baseDao;
	@Inject
	CrmDao crmDao;
	@Inject
	ServiceDao serviceDao;
	@Inject
	WorkflowDao workflowDao;
	
	@Inject
	SynchroCrm synchroCrm;

	/**
	 * 保存一个实体类(数据中心)
	 * @param entity
	 * @return
	 */
	public boolean saveEntity(Object entity){
		return baseDao.saveEntity(entity);
	}

	/**
	 * 保存一个实体类(Service)
	 * @param entity
	 * @return
	 */
	public void saveServiceEntity(Object entity){
		serviceDao.saveEntity(entity);
	}

	
	
	
	
	/**
	 * 从oa同步数据到数据中心
	 * 
	 * @param sql
	 *            查询表语句
	 * @param requiredType
	 *            对象
	 * @param delSql
	 *            删除表语句
	 * 
	 */
	public <T> void loadData(String sql, Class<T> requiredType) {
		int index = 0;
		while (index >= 0) {
			SqlPageQuery<T> sqlPageQuery = new SqlPageQuery<>(requiredType, Constant.PROCESS_DATA_LIMIT, index, sql);
			PageResult<T> pageResult = this.baseDao.findDataWithPage(sqlPageQuery);
			if (pageResult.getReturnDataSize() == 0)
				return;
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				T obj = pageResult.getData().get(i);
				try {
					boolean flag=baseDao.updateEntity(obj);
					if(!flag){
						baseDao.saveEntity(obj);
					}
				} catch (Exception e) {
					logger.error("数据库异常", e);
				}
			}
			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}

	}
	
	/**
	 * @Title: loadBaseDataToService
	 * @Description: 从数据中心同步数据到service系统
	 * @author 张庆奇
	 * @param sql
	 * @param requiredType
	 * @param delSql
	 * @return void
	 * @throws @date
	 *             2016年10月17日 下午2:27:58
	 */
	public <T> void loadBaseDataToService(String sql, Class<T> requiredType, String delSql) {
		int index = 0;
		while (index >= 0) {
			SqlPageQuery<T> sqlPageQuery = new SqlPageQuery<>(requiredType, Constant.PROCESS_DATA_LIMIT, index, sql);
			PageResult<T> pageResult = this.baseDao.findDataWithPageByMySql(sqlPageQuery);
			if (pageResult.getReturnDataSize() == 0)
				return;
			Integer count = pageResult.getDbRowCount();
			if (index == 0 && count > 0) {
				if (!"".equals(delSql)) {
					// 有数据先清空该表数据
					serviceDao.deleteEntity(delSql);
				}
			}
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				T obj = pageResult.getData().get(i);
				try {

					serviceDao.saveEntity(obj);
				} catch (Exception e) {
					logger.error("数据库异常", e);

				}
			}

			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}

	}

	/**
	 * @Title: loadServiceData
	 * @Description: 从o2o_service 表同步数据到数据中心
	 * @author 张庆奇
	 * @param sql
	 * @param requiredType
	 * @param delSql
	 * @return void
	 * @throws @date
	 *             2016年9月21日 下午5:36:09
	 */
	public <T> void loadServiceData(String sql, Class<T> requiredType, String delSql) {
		int index = 0;
		while (index >= 0) {
			SqlPageQuery<T> sqlPageQuery = new SqlPageQuery<>(requiredType, Constant.PROCESS_DATA_LIMIT, index, sql);
			PageResult<T> pageResult = this.serviceDao.findDataWithPageForService(sqlPageQuery);
			if (pageResult.getReturnDataSize() == 0)
				return;
			Integer count = pageResult.getDbRowCount();
			if (index == 0 && count > 0) {
				if (!"".equals(delSql)) {
					// 有数据先清空该表数据
					baseDao.deleteEntity(delSql);
				}
			}
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				T obj = pageResult.getData().get(i);
				try {

					baseDao.saveEntity(obj);
				} catch (Exception e) {
					logger.error("数据库异常", e);

				}
			}

			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}

	}

	/**
	 * 更新或者插入表部分数据(仅贷中数据同步使用，其他如果使用不能改变这个方法)
	 * 
	 * @param sql
	 *            查询sql语句
	 * @param delSql
	 *            删除sql语句
	 * @param exeSql
	 *            执行更新或者添加的sql语句
	 */
	public void loadPartPartData(String sql, String delSql, String exeSql, String findEntitySql) {
		int index = 0;
		while (index >= 0) {
			SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Constant.PROCESS_DATA_LIMIT, index,
					sql);
			PageResult<Object[]> pageResult = this.baseDao.findDataWithPage(sqlPageQuery);
			if (pageResult.getReturnDataSize() == 0)
				return;
			Integer count = pageResult.getDbRowCount();
			if (index == 0 && count > 0) {
				if (!"".equals(delSql)) {
					// 有数据先清空该表数据
					baseDao.deleteEntity(delSql);
				}
			}
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				Object[] objs = pageResult.getData().get(i);

				try {

					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					Timestamp time = (Timestamp) objs[0];
					String tsStr = sdf.format(time);
					Date date = sdf.parse(tsStr);
					BigDecimal loanapplyno = (BigDecimal) objs[1];
					String loanapplynoStr = "";
					if (loanapplyno != null) {
						loanapplynoStr = loanapplyno.toString();
					}

					Integer flag = baseDao.countMysql(findEntitySql, date, loanapplynoStr);

					if (flag == null || flag == 0) {
						baseDao.excuteSql(exeSql, date, loanapplynoStr);
					}
				} catch (Exception e) {
					e.printStackTrace();
					logger.error("主键", objs[0]);
					logger.error("数据库异常", e);

				}
			}

			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}

	}

	/**
	 * @Title: loadCrmData
	 * @Description: 从crm同步数据到数据中心
	 * @author 张庆奇
	 * @param sql
	 * @param requiredType
	 * @param delSql
	 * @return void
	 * @throws @date
	 *             2016年11月22日 下午1:17:23
	 */
	public <T> void loadCrmData(String sql, Class<T> requiredType, String delSql) {
		int index = 0;
		int total = 0;
		while (index >= 0) {
			SqlPageQuery<T> sqlPageQuery = new SqlPageQuery<>(requiredType, Constant.PROCESS_DATA_LIMIT, index, sql);
			PageResult<T> pageResult = this.crmDao.findDataWithPageForMysql(sqlPageQuery);
			if (pageResult.getReturnDataSize() == 0)
				return;
			Integer count = pageResult.getDbRowCount();
			if (index == 0 && count > 0) {
				if (!"".equals(delSql)) {
					// 有数据先清空该表数据
					baseDao.deleteEntity(delSql);
				}
			}

			logger.debug("total:{},pagesize:{}", ++total, pageResult.getReturnDataSize());

			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				T obj = pageResult.getData().get(i);
				try {
					baseDao.saveEntity(obj);
				} catch (Exception e) {
					logger.error("数据库异常", e);
				}
			}

			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}

	}

	/**
	 * @Title: updateCustomerLoanNum
	 * @Description: 更新数据中心放款次数
	 * @author 张庆奇
	 * @param sql
	 * @param exeSql
	 * @return void
	 * @throws @date
	 *             2016年8月8日 下午3:24:33
	 */
	public void updateCustomerLoanNum(String sql, String exeSql) {
		int index = 0;
		int total = 0;
		while (index >= 0) {

			SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Constant.PROCESS_DATA_LIMIT, index,
					sql);
			PageResult<Object[]> pageResult = baseDao.findDataWithPageByMySql(sqlPageQuery);// 查询id,证件号
			logger.debug("total:{},pagesize:{}", ++total, pageResult.getReturnDataSize());
			if (pageResult.getReturnDataSize() == 0)
				return;
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				Object[] objs = pageResult.getData().get(i);
				int loanNum = 0;
				try {
					loanNum = queryLoanCount(objs[2].toString());

					baseDao.excuteSql(exeSql, loanNum, objs[0]);
					crmDao.excuteSql("update customer_basic_info set number_of_loans=?  where customer_id=? ", loanNum,objs[0]);
					logger.info("客户id:{},放款次数为:{}", objs[0].toString(), loanNum);

				} catch (Exception e) {
					logger.info("客户id:{},放款次数为:{}", objs[0].toString(), loanNum);
					logger.error("主键", objs[0]);
					logger.error("更新数据中心放款次数,数据库异常", e);

				}
			}

			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}

	}
	
	
	/**
	 * @Title: updateCustomerFirstLoanTime
	 * @Description: 更新第一次放款时间
	 * @author 张庆奇
	 * @param sql
	 * @param exeSql
	 * @return void
	 * @throws
	 * @date 2016年12月7日 上午9:57:02
	 */
	public void updateCustomerFirstLoanTime(String sql,String exeSql) {
		int index = 0;
		int total = 0;
		while (index >= 0) {

			SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Constant.PROCESS_DATA_LIMIT, index,
					sql);
			PageResult<Object[]> pageResult = baseDao.findDataWithPageByMySql(sqlPageQuery);// 查询id,证件号
			logger.debug("total:{},pagesize:{}", ++total, pageResult.getReturnDataSize());
			if (pageResult.getReturnDataSize() == 0)
				return;
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				Object[] objs = pageResult.getData().get(i);
				String first_date = "";
				try {
					first_date = queryLoanFistTime(objs[2].toString());
					if (StringUtil.isNotBlank(first_date)) {
						baseDao.excuteSql(exeSql,first_date, objs[0]);
						crmDao.excuteSql("update customer_basic_info set first_loan_time=? where customer_id=? ", first_date, objs[0]);
						logger.info("客户id:{},第一次放款时间为:{}", objs[0].toString(), first_date);
					}

				} catch (Exception e) {
					logger.info("客户id:{},第一次放款时间为:{}", objs[0].toString(),first_date);
					logger.error("主键", objs[0]);
					logger.error("更新数据中心第一次放款时间,数据库异常", e);

				}
			}

			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}

	}

	/**
	 * @Title: queryLoanCount
	 * @Description: 查询放款次数
	 * @author 张庆奇
	 * @param idcard
	 * @return
	 * @return Integer
	 * @throws @date
	 *             2016年8月16日 下午1:32:45
	 */
	public Integer queryLoanCount(String idcard) {
		String sql = "SELECT count(1) FROM loan_base_info where loan_time is not null and idcard=? ";
		
		int count = baseDao.countMysql(sql, idcard);
		return count ;
	}

	/**
	 * @Title: queryLoanFistTime
	 * @Description:查询第一次放款时间
	 * @author 张庆奇
	 * @param idcard
	 * @return
	 * @return String
	 * @throws @date
	 *             2016年8月16日 下午1:33:15
	 */
	public String queryLoanFistTime(String idcard) {
		String sql = "SELECT min(loan_time) as first_loan_time FROM loan_base_info where loan_time is not null and idcard=? ";

		O2oCustomer customer = (O2oCustomer) baseDao.findEntityForMysql(sql, O2oCustomer.class, idcard);
		String first_date = "";
		if (customer != null && customer.getFirstLoanTime() != null) {
			first_date = DateUtils.formateStandTime(customer.getFirstLoanTime());
		}

		return first_date;
		
		
	}

	/**
	 * @插入 sql
	 */
	public void excute(String sql,Object... params) {

		baseDao.excuteSql(sql,params);

	}

	public void updateFKDate() {
		// 更新贷款时间和贷款状态
		logger.info("updateFKDate-贷中更新贷款时间和贷款状态开始", "");
		int pIndex = 0;
		int pSize = 100;
		String pSql = "select str_to_date(date_format(t.loan_actual_time,'%Y-%m-%d'),'%Y-%m-%d'),t.loanapply_no,t.bill_code from o2o_loan_oaoverdue t where t.loan_status='Y' ";
		while (pIndex >= 0) {
			SqlPageQuery<Object[]> PageQuery = new SqlPageQuery<>(Object[].class, pSize, pIndex, pSql);
			PageResult<Object[]> r = serviceDao.findDataWithPageForService(PageQuery);
			List<Object[]> list = r.getData();
			if (list != null && list.size() > 0) {
				for (int i = 0; i < list.size(); i++) {
					Object[] b = list.get(i);
					List<Object[]> ls = baseDao.findParams(
							"select count(0) from basic_information_loan_ing where bank_loan_date is not null  and loanapplyno = ? ",
							b[1] != null ? (String) b[1] : "");
					if (ls != null && ls.size() > 0) {
						Object[] o = ls.get(0);
						long count = (long) o[0];
						if (count > 0) {
							baseDao.excuteSql(
									"update basic_information_loan_ing set bank_loan_date = ?	 where loanapplyno = ?  ",
									b[0] != null ? (Date) b[0] : "", b[1] != null ? (String) b[1] : "");
						}
					}
					/*
					 * List<Object[]> ls1 = baseDao.findParams(
					 * "select count(0) from report_refund_add where loan_date is not null  and bill_code = ? "
					 * , b[2] != null ? (String) b[2] : ""); if (ls1 != null &&
					 * ls1.size() > 0) { Object[] o = ls1.get(0); long count =
					 * (long) o[0]; if (count > 0) { baseDao.excuteSql(
					 * "update report_refund_add set loan_date = ?	 where bill_code = ?  "
					 * , b[0] != null ? (Date) b[0] : "", b[2] != null ?
					 * (String) b[2] : ""); } }
					 */
				}
			}
			pIndex++;
			if (r.getPageTotal() <= pIndex) {
				pIndex = -1;
			}
		}
		logger.info("updateFKDate-贷中更新贷款时间和贷款状态结束", "");
	}

	public List<Object[]> findObjectsList(String sql) {
		return baseDao.findObjectsList(sql);
	}

	/**
	 * @Title: updateOldCustomerBasicInfo
	 * @Description: 更新customer_basic_info_old 表中mate_id 字段
	 * @author 张庆奇
	 * @param sql
	 * @return void
	 * @throws @date
	 *             2016年8月26日 下午2:16:07
	 */
	public void updateOldCustomerBasicInfo(String sql) {

		{
			int index = 0;
			int total = 0;
			while (index >= 0) {

				SqlPageQuery<O2oLoanUser> sqlPageQuery = new SqlPageQuery<>(O2oLoanUser.class,
						Constant.PROCESS_DATA_LIMIT, index, sql);
				PageResult<O2oLoanUser> pageResult = baseDao.findDataWithPageByMySql(sqlPageQuery);// 查询id,证件号
				logger.debug("total:{},pagesize:{}", ++total, pageResult.getReturnDataSize());
				if (pageResult.getReturnDataSize() == 0)
					return;
				for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
					O2oLoanUser entity = pageResult.getData().get(i);
					String mate_id = "";
					try {

						if (StringUtils.isNotEmpty(entity.getSpouseId())) {
							mate_id = entity.getSpouseId();
						} else {
							O2oLoanUser user = (O2oLoanUser) baseDao.findEntityForMysql(
									"select * from o2o_loan_user where loanapply_no=? and spouse_id is not null",
									O2oLoanUser.class, entity.getLoanapplyNo());
							if (user != null && StringUtils.isNotEmpty(user.getSpouseId())) {
								mate_id = user.getLoanId();
							}
						}
						if (StringUtils.isNotEmpty(mate_id)) {
							baseDao.excuteSql("update customer_basic_info_old set mate_id=? where loan_id=?", mate_id,
									entity.getLoanId()); // 更新到报表数据库
						}

					} catch (Exception e) {
						e.printStackTrace();
						logger.error("主键", entity.getLoanId());

					}
				}

				if (pageResult.getPageTotal() > index + 1) {
					index++;
				} else {
					index = -1;
				}
			}

		}
	}

	/**
	 * @Title: loadOaShopInfoData
	 * @Description: 同步oa门店信息
	 * @author 张庆奇
	 * @param sql
	 * @param requiredType
	 * @param delSql
	 * @return void
	 * @throws @date
	 *             2016年11月22日 下午2:34:06
	 */
	public <T> void loadOaShopInfoData(String sql, Class<T> requiredType, String delSql) {
		int index = 0;
		while (index >= 0) {
			SqlPageQuery<CustomerShopInfoEntity> sqlPageQuery = new SqlPageQuery<>(CustomerShopInfoEntity.class,
					Constant.PROCESS_DATA_LIMIT, index, sql);
			PageResult<CustomerShopInfoEntity> pageResult = this.baseDao.findDataWithPage(sqlPageQuery);
			if (pageResult.getReturnDataSize() == 0)
				return;
			Integer count = pageResult.getDbRowCount();
			if (index == 0 && count > 0) {
				if (!"".equals(delSql)) {
					// 有数据先清空该表数据
					baseDao.deleteEntity(delSql);
				}
			}
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				CustomerShopInfoEntity obj = pageResult.getData().get(i);
				try {
					int num = baseDao.countMysql("select count(1) from  customer_shop_info where customer_id_number=? ",
							obj.getCustomerIdNumber());
					if (num == 0) {
						baseDao.saveEntity(obj);
					}
				} catch (Exception e) {
					logger.error("数据库异常", e);

				}
			}

			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}

	}

	/**
	 * @Title: loadNewCrmShopInfoData
	 * @Description: 同步新系统的门店信息
	 * @author 张庆奇
	 * @param sql
	 * @return void
	 * @throws @date
	 *             2016年11月22日 下午2:36:09
	 */
	public <T> void loadNewCrmShopInfoData(String sql) {
		int index = 0;
		int total = 0;
		while (index >= 0) {
			SqlPageQuery<CustomerShopInfoEntity> sqlPageQuery = new SqlPageQuery<>(CustomerShopInfoEntity.class,
					Constant.PROCESS_DATA_LIMIT, index, sql);
			PageResult<CustomerShopInfoEntity> pageResult = this.crmDao.findDataWithPageForMysql(sqlPageQuery);

			logger.debug("total:{},pagesize:{}", ++total, pageResult.getReturnDataSize());
			if (pageResult.getReturnDataSize() == 0)
				return;
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				CustomerShopInfoEntity obj = pageResult.getData().get(i);
				try {
					int num = baseDao.countMysql("select count(1) from  customer_shop_info where customer_id_number=? ",
							obj.getCustomerIdNumber());
					if (num == 0) {
						baseDao.saveEntity(obj);
					} else {
						if (obj.getSourceType() == 1) {
							baseDao.updateEntity(obj);// 新系统有，更新数据
						} else {
							// 老系统有数据，删除该数据，新增数据
							baseDao.excuteSql("delete from customer_shop_info where customer_id_number=?",
									obj.getCustomerIdNumber());
							baseDao.saveEntity(obj);
						}
					}
				} catch (DataIntegrityViolationException e) {
					logger.error("数据库异常", e);
				}
			}

			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}

	}

	/**
	 * @Title: loadCrmCustomerBaseData
	 * @Description: 从crm同步客户数据到数据中心
	 * @author 张庆奇
	 * @param sql
	 * @return void
	 * @throws @date
	 *             2016年12月2日 上午10:36:33
	 */
	public <T> void loadCrmCustomerBaseData(String sql) {
		int index = 0;
		int total = 0;
		while (index >= 0) {
			SqlPageQuery<O2oCustomer> sqlPageQuery = new SqlPageQuery<>(O2oCustomer.class, Constant.PROCESS_DATA_LIMIT,
					index, sql);
			PageResult<O2oCustomer> pageResult = this.crmDao.findDataWithPageForMysql(sqlPageQuery);

			logger.debug("total:{},pagesize:{}", ++total, pageResult.getReturnDataSize());
			if (pageResult.getReturnDataSize() == 0)
				return;
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				O2oCustomer obj = pageResult.getData().get(i);
				try {
					int num = baseDao.countMysql("select count(1) from o2o_customer where customer_id=?",
							obj.getCustomerId());
					if (num == 0) {
						baseDao.saveEntity(obj);
					} else {
						baseDao.updateEntity(obj);
					}
				} catch (DataIntegrityViolationException e) {
					logger.error("数据库异常", e);
				}

				if (pageResult.getPageTotal() > index + 1) {
					index++;
				} else {
					index = -1;
				}
			}
		}
	}

}
